<?php
class Model_message extends CI_Model {

	/*
	 * 1. var $table digunakan untuk menginisialisasi nama table dari model yang bersangkutan
	 * 2. variable ini ditaruh di global agar bisa di akses oleh semua function
	 * 3. Hal ini juga dimaksudkan apabila terjadi perubahan nama table, tinggal mengganti 1 saja
	 */
	var $table = "messages";
	
    function Model_User()
    {
        // Call the Model constructor
        parent::__construct();
    }
	
	/*
	 * function get_by($id);
	 * function ini digunakan untuk mengambil singgle row saja.
	 * digunakan untuk mengambil data ketika edit data.
	 */
	function get_by($id){
		$this->load->database();
						
			$sql = "SELECT {$this->table}.*, users.nama as nama_user, profile_image, username FROM {$this->table} LEFT JOIN users ON user_id=users.id  WHERE {$this->table}.id='$id';";
			$query = $this->db->query($sql);
		
		$this->db->close();
		
		return $query;
	}
	
	function get_detail($id){
		$this->load->database();
						
		//mengambil data dengan ada limitnya
		$sql = "SELECT {$this->table}.*, users.nama as nama_user, profile_image, username FROM {$this->table} LEFT JOIN users ON user_id=users.id  WHERE {$this->table}.id='$id';";
		$query = $this->db->query($sql);
		
		$count=0;
		$data = array(array());
		foreach($query->result_array() as $row){
			
			
			$sql = "SELECT {$this->table}.*, users.nama as nama_user, profile_image, username FROM {$this->table} LEFT JOIN users ON user_id=users.id  WHERE parent_id=$row[id] ORDER BY {$this->table}.created ASC;";
			$query_sub = $this->db->query($sql);
			
			$data[$count]['feed'] = $row;
			$data[$count]['comm'] = $query_sub;
			
			$count++;
		}
		$this->db->close();
		
		return $data;
	}
	
	/*
	 * function get_all();
	 * function ini digunakan untuk mengambil banyak record.
	 * digunakan ketika ingin melihat data dari table tsb.
	 */
	function get_all($page=0,$limit=20, $q='',$published='',$uid=''){
		$this->load->database();
		//$filter=" WHERE {$this->table}.id IN (SELECT DISTINCT (IF(parent_id=0,id, parent_id)) id FROM messages WHERE published='yes' ORDER BY {$this->table}.created DESC LIMIT $page,$limit) ";
		
		$filter='';
		$page = $page*$limit;
		if($q != ''){
			$filter = " WHERE (";
			$fields = $this->db->list_fields("{$this->table}");
			foreach ($fields as $field){
			   $filter .= " {$this->table}.$field LIKE '%$q%' OR" ;
			}
			$filter = rtrim($filter,'OR').")";
		}
		
		if($published != ''){
			if($uid!='')
				$filter .= " ".(strpos($filter,"WHERE") === false?"WHERE":"AND")." {$this->table}.published='$published'";
		}
		
		if($uid != ''){
			if($uid!='')
				$filter .= " ".(strpos($filter,"WHERE") === false?"WHERE":"AND")." {$this->table}.user_id='$uid'";
		}
		
		//mengambil data dengan ada limitnya
		$sql = "SELECT {$this->table}.*,current, users.nama as nama_user, profile_image, username FROM (SELECT {$this->table}.*, child,current FROM messages LEFT JOIN (SELECT IF(parent_id=0,id, parent_id) parent, id child, created current FROM {$this->table} WHERE published='yes' ORDER BY {$this->table}.created DESC) sub ON messages.id = sub.parent WHERE child<>'' AND parent_id=0 GROUP BY id ORDER BY child DESC){$this->table} LEFT JOIN users ON user_id=users.id  $filter LIMIT $page,$limit;";
		$query = $this->db->query($sql);
		
		$count=0;
		$data = array(array());
		foreach($query->result_array() as $row){		
			
			$sql = "SELECT {$this->table}.*, users.nama as nama_user, profile_image, username FROM {$this->table} LEFT JOIN users ON user_id=users.id  WHERE parent_id=$row[id] ORDER BY {$this->table}.created ASC;";
			$query_sub = $this->db->query($sql);
			
			$data[$count]['feed'] = $row;
			$data[$count]['comm'] = $query_sub;
			
			$count++;
		}
		$this->db->close();
		
		return $data;
	}
	
	function get_comment($id=0,$date=0){
		$this->load->database();
			$filter='';
			if($id != 0){
				$filter="{$this->table}.parent_id=$id ORDER BY {$this->table}.created DESC";
			}
			
			if($date != 0){
				$filter="{$this->table}.parent_id<>0 AND {$this->table}.created > '$date' ORDER BY {$this->table}.created ASC";
			}
			
			$sql = "SELECT {$this->table}.*, users.nama as nama_user, profile_image, username FROM {$this->table} LEFT JOIN users ON user_id=users.id WHERE $filter;";
			$query = $this->db->query($sql);
		$this->db->close();
		
		return $query;
	}
	
	function get_activity(){
		$this->load->database();
			$sql = "SELECT {$this->table}.*, users.nama as nama_user, profile_image, username FROM {$this->table} LEFT JOIN users ON user_id=users.id ORDER BY {$this->table}.created DESC LIMIT 6;";
			$query = $this->db->query($sql);
		$this->db->close();
		
		return $query;
	}
	
	function get_feed($page=0,$limit=20, $time,$top){
		$this->load->database();
		
		$page = $page*$limit;

		if($top)
			$sql = "SELECT {$this->table}.*, created current, users.nama as nama_user, profile_image, username FROM {$this->table} LEFT JOIN users ON user_id=users.id  WHERE parent_id=0 AND created > '$time' ORDER BY {$this->table}.created DESC LIMIT $page,$limit;";
		else
			$sql = "SELECT {$this->table}.*, current, users.nama as nama_user, profile_image, username FROM (SELECT {$this->table}.*, child,current FROM messages LEFT JOIN (SELECT IF(parent_id=0,id, parent_id) parent, id child, created current FROM {$this->table} WHERE published='yes' AND {$this->table}.created < '$time' ORDER BY {$this->table}.created DESC) sub ON messages.id = sub.parent WHERE child<>'' AND parent_id=0 GROUP BY id ORDER BY child DESC) {$this->table} LEFT JOIN users ON user_id=users.id LIMIT $page,$limit;";
		//mengambil data dengan ada limitnya
		$query = $this->db->query($sql);
		
		$count=0;
		$data = array();
		foreach($query->result_array() as $row){		
			
			$sql = "SELECT {$this->table}.*, users.nama as nama_user, profile_image, username FROM {$this->table} LEFT JOIN users ON user_id=users.id  WHERE parent_id=$row[id] ORDER BY {$this->table}.created ASC;";
			$query_sub = $this->db->query($sql);
			
			$data[$count] = $row;
			$data[$count]['comm'] = $query_sub->result_array();
			
			$count++;
		}
		$this->db->close();
		
		return $data;
	}
    
	/*
	 * function add();
	 * digunakan untuk menambahkan data
	 * parameter $data adalah array dimana key dari array tsb adalah nama field 
	   dan value dari arrat tsb adalah record yang akan diinsertakan
	 */
	function add($data){
		$this->load->database();
		
		$this->db->insert("{$this->table}",$data);
		
		$return = $this->db->insert_id();
		
		$this->db->close();
		
		return $return;
	}
	
	/*
	 * function edit();
	 * digunakan untuk mengedit data
	 * parameter $data adalah array dimana key dari array tsb adalah nama field 
	   dan value dari arrat tsb adalah record yang akan diinsertakan
	 * parameter $id adalah id dari data yang akan diedit
	 */
	 
	function edit($id,$data){
		$this->load->database();
			$this->db->where('id',$id);
			$this->db->update("{$this->table}",$data);
		$this->db->close();
	}
	
	/*
	 * function delete();
	 * digunakan untuk menghapus data
	 * parameter $id adalah id dari data tersebut
	 */
	function delete($id){
		$this->load->database();
		
		$this->db->trans_start();
			$this->db->query("DELETE FROM {$this->table} WHERE id='$id'");
		$this->db->trans_complete();
		
		$return = $this->db->trans_status();
		
		$this->db->close();
		return $return;
	}
}
?>